package com.labmi.experiment.dao;

import java.io.Serializable;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityResult;
import javax.persistence.FieldResult;
import javax.persistence.NamedNativeQueries;
import javax.persistence.NamedNativeQuery;
import javax.persistence.NoResultException;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.SqlResultSetMapping;

import com.labmi.experiment.ClothingItem;
import com.labmi.experiment.Item;


public class ItemDAO implements GenericDao<Item, Serializable> {

	private EntityManager em;
	
	private String categoryId; 
	
	private List<Item> cachedResult; //Used as cache  
	
	public ItemDAO() {
		em = EntityManagerSingleton.getInstance(); 
	}
	
	@Override
	public Serializable create(Item newInstance) {
		return null;
	}

	@Override
	public Item read(Serializable id) {
		System.out.println("Finding item with id: " + id);
		
		Item item = null;
		try {
			Query query = em.createQuery("SELECT x  FROM ClothingItem x WHERE x.id = ?1").setParameter(1, id);
			
			item = (Item) query.getSingleResult();
			
			System.out.println("Found item with id: " + id);
		} catch (NoResultException e) {
			
			System.out.println("No element found with id: " + id);
		}
		
		
		
		return item;
	}

	@Override
	public void update(Item transientObject) {
		em.getTransaction().begin();
		
		em.merge(transientObject);
		
		em.getTransaction().commit();
		
	}

	@Override
	public void delete(Item persistentObject) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public List<Item> fetchAll(int limit) {
		
		System.out.println("Fetching all items from database...");
		
		Query queryResult = em.createQuery("SELECT x  FROM ClothingItem x");
		
		List<Item> resultList = queryResult.getResultList();
		System.out.println("Items fetched: " + resultList.size());
		for (Item anItem : resultList) {
			System.out.println(anItem.getName());
		}
				
		return resultList;
	}
	
	/**
	 * For performance reasons, the method access DB only once for retrieving items 
	 * in the specified category id.
	 * Other calls to this method always return the old result set.
	 * 
	 * When user select different category ID the result set access DB again but always once!
	 * 
	 * 
	 * @param userSelectedCategoryID
	 * @return The list of items in the selected category
	 */
	public List<Item> fetchItemByCategoryId(String userSelectedCategoryID)
	{
		 
		
												
		
		//Skip when the category id is the same as before. 
		if(this.categoryId == null || ! this.categoryId.equals(userSelectedCategoryID))
		{
			System.out.println("Fetching all items with root category id: " + userSelectedCategoryID);
			//										NAMED QUERY DEFINED INSIDE ENTITY CLASS: ClothingItem.class
			Query queryResult = em.createNamedQuery("rootCategoryItemList").setParameter(1, userSelectedCategoryID);
			cachedResult = queryResult.getResultList();
			
			this.categoryId = userSelectedCategoryID; //SAVE LAST CATEGORY SELECTION
		}
		
		if(cachedResult == null)
			cachedResult = new ArrayList<Item>(); //Prevent null pointer....
		
		return cachedResult;
	}

	/*
	SELECT 
	clothingitem.id, 			clothingitem.name, clothingitem.description,  	clothingitem.price, 
	clothingitem.detailImage 
 FROM clothingitem join ( 
 SELECT root.id AS root_id, root.name AS root_name, secondCategory.id AS secondCategory_id, secondCategory.name AS secondCategory_name, thirdCategory.id AS thirdCategory_id, thirdCategory.name AS thirdCategory_name
 FROM category AS root
 LEFT OUTER JOIN category AS secondCategory ON secondCategory.parent = root.id
 LEFT OUTER JOIN category AS thirdCategory ON thirdCategory.parent = secondCategory.id
 WHERE root.parent =0) AS CAT_STRUCT on (category_id = CAT_STRUCT.thirdcategory_id AND CAT_STRUCT.root_id = ?)
	*/
	public List<Item> searchItemInsideCategory(String key, String category)
	{
		if(key != null && ! key.isEmpty() )
		{
			if(category != null && ! category.isEmpty() )
			{
				System.out.println("Searching all items inside category: " + category + " | search key: " + key);
				
				Query query = em.createNamedQuery("getItemsByCategoryAndKey").
								setParameter(1, category).
								setParameter(2, category).
								setParameter(3, category).
								setParameter(4, "%" + key + "%").
								setParameter(5,   "%" + key + "%");
				System.out.println(query.toString());
				List<Item> resultList = query.getResultList();
				
				System.out.println("Items fetched: " + resultList.size());
				
				cachedResult = query.getResultList();
				
				
			}
			
			if(cachedResult == null)
				cachedResult = new ArrayList<Item>(); //Prevent null pointer if result set is null....
		}
		
		return cachedResult;
	}
	
	
}
